/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package util;

import java.sql.CallableStatement;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

/**
 *
 * @author Gabriel Menezes
 */
public class Connector {

    private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DATABASE_URL = "jdbc:mysql://localhost/clin";
    private static final String USER = "root";
    private static final String PASSWORD = "adminadmin";

    public static final int TYPE_INTEGER = Types.INTEGER;
    public static final int TYPE_STRING = Types.VARCHAR;
    public static final int TYPE_DOUBLE = Types.DOUBLE;
    public static final int TYPE_BOOLEAN = Types.BOOLEAN;


    public static final int IN_PARAMETER = -2;
    public static final int OUT_PARAMETER = -3;

    Connection connection = null;

    /**
     * Construtor obtem uma nova conexão
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public Connector() throws ClassNotFoundException, SQLException {
        Class.forName(JDBC_DRIVER);
        connection = DriverManager.getConnection(DATABASE_URL, USER, PASSWORD);
    }

    /**
     * Executa uma Query simples do tipo SELECT
     * no Banco de Dados retornando o conjunto selecionado
     * @param sqlQuery Query a ser executada
     * @return Um ResultSet com o resultado da query
     * @throws SQLException
     */
    public ResultSet executeSelectQuery(String sqlQuery) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sqlQuery);
        return preparedStatement.executeQuery();
    }

    /**
     * Executa uma Query no Banco de Dados do tipo UPDATE
     * @param sqlQuery Query a ser executada
     * @throws SQLException
     */
    public void executeUpdateQuery(String sqlQuery) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sqlQuery);
        preparedStatement.executeUpdate();
    }

    /**
     * Executa uma Query simples do tipo INSERT
     * no Banco de Dados retornando a chave gerada
     * @param sqlQuery Query a ser executada
     * @return Chave gerada pela inserção
     * @throws SQLException
     */
    public int executeInsertQuery(String sqlQuery) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sqlQuery, Statement.RETURN_GENERATED_KEYS);
        preparedStatement.executeUpdate();

        ResultSet key = preparedStatement.getGeneratedKeys();
        key.next();

        int generatedKey = key.getInt(1);

        return generatedKey;
    }

    /**
     * Função que executa a chamada a uma Stored Function
     * @param sql Texto SQL com a chamada à função
     * @param args Argumentos de entrada
     * @param types Tipos dos argumentos de entrada
     * @return Valor retornado pela Stored Function
     * @throws SQLException
     */
    public int executeStoredFunctionCall(String sql, Object[] args, int[] types) throws SQLException {
        CallableStatement callableStatement = connection.prepareCall(sql);

        //Registra parâmetro de saída
        callableStatement.registerOutParameter(1, Types.INTEGER);

        //Registra parâmetros de entrada
        for (int i = 0; i < args.length; i++) {
            if (types[i] == TYPE_INTEGER)
                callableStatement.setInt(i + 2, Integer.parseInt(args[i].toString()));
            else if (types[i] == TYPE_STRING)
                callableStatement.setString(i + 2, args[i].toString());
            else if (types[i] == TYPE_STRING)
                callableStatement.setDouble(i + 2, Double.parseDouble(args[i].toString()));

        }

        //Executa chamada
        callableStatement.execute();

        return callableStatement.getInt(1);
    }


    /**
     * Função que executa a chamada a uma Stored Procedure
     * @param sql Chamada à Procedure
     * @param args Argumentos de entrada e saída
     * @param types Tipo de dado de cada argumento
     * @param inOut Informar se o argumento é de entrada ou saída
     * @throws SQLException
     */
    public void executeStoredProcedureCall(String sql, Object[] args, int[] types, int[] inOut) throws SQLException {
        CallableStatement callableStatement = connection.prepareCall(sql);

        //Registra parâmetros
        if (args != null) {
            for (int i = 0; i < args.length; i++) {
                if (inOut[i] == IN_PARAMETER) {
                    if (types[i] == TYPE_INTEGER) {
                        callableStatement.setInt(i + 1, Integer.parseInt(args[i].toString()));
                    } else if (types[i] == TYPE_STRING) {
                        callableStatement.setString(i+1, args[i].toString());
                    } else if (types[i] == TYPE_DOUBLE ) {
                        callableStatement.setDouble(i+1, Double.parseDouble(args[i].toString()));
                    }
                } else if (inOut[i] == OUT_PARAMETER) {
                    callableStatement.registerOutParameter(i, types[i]);
                }
            }
        }

        //Executa chamada
        callableStatement.execute();

        //Obtém as saídas
        if (args != null) {
            for (int i = 0; i < args.length; i++) {
                if (inOut[i] == OUT_PARAMETER) {
                    if (types[i] == TYPE_INTEGER) {
                        args[i] = callableStatement.getInt(i);
                    } else if (types[i] == TYPE_STRING) {
                        args[i] = callableStatement.getString(i);
                    }
                }
            }
        }
    }
}
